import pandas as pd
from openpyxl import load_workbook
import glob


def read_file():
    print("程序已经开始运行,文件读取中...")
    file = glob.glob('./*.xlsx')[0]  # 假设只找到一个文件，如果有多个文件需要根据实际情况处理

    # 读取首揽明细表
    df1 = pd.read_excel(r'.\C2B取件首揽日跟进明细表.xlsb', header=1,
                        usecols=[11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26])
    df1.rename(columns={'片区.1': '片区'})
    df1 = df1[df1['片区'] == '豫西片区']
    # 读取揽收及时率明细表
    df2 = pd.read_excel(r'.\C2B取件首揽日跟进明细表.xlsb', sheet_name='揽收及时率明细',
                        usecols=[6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18])
    df2.rename(columns={'片区.1': '片区'})
    df2 = df2[df2['片区'] == '豫西片区']

    # 打开工作簿
    book = load_workbook(file)
    sheet_name1 = '首揽明细'
    sheet_name2 = '揽收及时率明细'

    if sheet_name1 not in book.sheetnames:
        book.create_sheet(sheet_name1)

    if sheet_name2 not in book.sheetnames:
        book.create_sheet(sheet_name2)

    # 获取工作表
    sheet1 = book[sheet_name1]
    sheet2 = book[sheet_name2]

    # 写入首揽明细
    write_data_to_excel(df1, sheet1, start_row=2, start_col=12)

    # 写入揽收及时率明细
    write_data_to_excel(df2, sheet2, start_row=2, start_col=7)

    book.save(file)
    print('写入完成')


def write_data_to_excel(data, sheet, start_row, start_col):
    # 获取数据数组
    data_array = data.values

    for i in range(data_array.shape[0]):
        for j in range(data_array.shape[1]):
            sheet.cell(row=start_row + i, column=start_col + j).value = data_array[i, j]


print()
if __name__ == '__main__':
    read_file()
